/*
 * @Author: xiaosihan
 * @Date: 2025-08-24 10:00:00
 * @Last Modified by: xiaosihan
 * @Last Modified time: 2025-11-07 00:01:25
 */

import express from "express";
import database from "../database";
import ServiceBase from "./ServiceBase";
import { QueryTypes } from 'sequelize';

// MySQL事件调度服务
class MySqlEventService extends ServiceBase {

    constructor(req: express.Request = {} as express.Request, res: express.Response = {} as express.Response) {
        super(req, res);
    }

    // 获取所有事件
    get_events = async (params: Record<string, any>) => {
        const events = await database.sequelize.query(
            `SHOW EVENTS`,
            { type: QueryTypes.SELECT }
        );
        return events;
    };

    // 获取事件状态
    get_event_status = async (params: Record<string, any>) => {
        const status = await database.sequelize.query(
            `SELECT @@global.event_scheduler AS event_scheduler`,
            { type: QueryTypes.SELECT }
        );
        return status[0];
    };

    // 开启事件调度器
    write_event_scheduler = async (params: Record<string, any>) => {
        await database.sequelize.query(`SET GLOBAL event_scheduler = ON`);
        return "开启成功";
    };

    // 关闭事件调度器
    write_event_scheduler_off = async (params: Record<string, any>) => {
        await database.sequelize.query(`SET GLOBAL event_scheduler = OFF`);
        return "关闭成功";
    };

    // 创建一次性事件
    write_event = async (params: Record<string, any>) => {
        // 参数校验
        this.verifyParams(['eventName', 'sqlStatement', 'executeAt']);

        const { eventName, sqlStatement, executeAt } = params;

        // 检查事件是否已存在
        const existingEvent = await database.sequelize.query(
            `SHOW EVENTS WHERE Name = :eventName`,
            { replacements: { eventName }, type: QueryTypes.SELECT }
        );

        if (existingEvent.length > 0) {
            throw new Error("事件名称已存在");
        }

        // 为SQL语句创建存储过程，避免直接拼接SQL
        const procedureName = `proc_${Date.now()}`;
        await database.sequelize.query(`
            CREATE PROCEDURE ${procedureName}()
            BEGIN
                ${sqlStatement};
            END
        `);

        // 创建一次性事件，调用存储过程
        await database.sequelize.query(
            `CREATE EVENT IF NOT EXISTS :eventName 
            ON SCHEDULE AT :executeAt 
            DO CALL ${procedureName}()`,
            {
                replacements: { eventName, executeAt }
            }
        );

        // 创建事件后删除存储过程，避免冲突
        await database.sequelize.query(`DROP PROCEDURE IF EXISTS ${procedureName}`);

        return "创建成功";
    };

    // 创建周期性事件
    write_event_recurring = async (params: Record<string, any>) => {
        // 参数校验
        this.verifyParams(['eventName', 'sqlStatement', 'startTime', 'intervalValue', 'intervalUnit']);

        const { eventName, sqlStatement, startTime, intervalValue, intervalUnit, endTime, comment } = params;

        // 检查事件是否已存在
        const existingEvent = await database.sequelize.query(
            `SHOW EVENTS WHERE Name = :eventName`,
            { replacements: { eventName }, type: QueryTypes.SELECT }
        );

        if (existingEvent.length > 0) {
            throw new Error("事件名称已存在");
        }

        // 为SQL语句创建存储过程，避免直接拼接SQL
        const procedureName = `proc_${Date.now()}`;
        await database.sequelize.query(`
            CREATE PROCEDURE ${procedureName}()
            BEGIN
                ${sqlStatement};
            END
        `);

        // 构建创建事件的SQL语句
        let createEventSql =
            `CREATE EVENT IF NOT EXISTS :eventName 
            ON SCHEDULE EVERY :intervalValue ${intervalUnit} STARTS :startTime 
            DO CALL ${procedureName}()`;

        const replacements: any = { eventName, intervalValue, startTime };

        if (endTime) {
            createEventSql = createEventSql.replace('DO', `ENDS :endTime DO`);
            replacements.endTime = endTime;
        }

        if (comment) {
            createEventSql += ` COMMENT :comment`;
            replacements.comment = comment;
        }

        // 执行创建事件的SQL语句
        await database.sequelize.query(createEventSql, {
            replacements: replacements
        });

        // 不删除存储过程，因为周期性事件需要多次调用

        return "创建成功";
    };

    // 启用事件
    write_event_enable = async (params: Record<string, any>) => {
        // 参数校验
        this.verifyParams(['eventName']);

        const { eventName } = params;

        // 添加参数化查询，防止SQL注入
        await database.sequelize.query(`ALTER EVENT ${database.sequelize.escape(eventName)} ENABLE`);
        return "启用成功";
    };

    // 禁用事件
    write_event_disable = async (params: Record<string, any>) => {
        // 参数校验
        this.verifyParams(['eventName']);

        const { eventName } = params;

        // 添加参数化查询，防止SQL注入
        await database.sequelize.query(`ALTER EVENT ${database.sequelize.escape(eventName)} DISABLE`);
        return "禁用成功";
    };

    // 删除事件
    del_event = async (params: Record<string, any>) => {
        // 参数校验
        this.verifyParams(['eventName']);

        const { eventName } = params;

        // 添加参数化查询，防止SQL注入
        await database.sequelize.query(`DROP EVENT IF EXISTS ${database.sequelize.escape(eventName)}`);
        return "删除成功";
    };

}

export default MySqlEventService;